/**
 * CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

package com.cmcc.zysoft.infocollection.dao;

import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;
import com.cmcc.zysoft.infocollection.model.Department;
import com.cmcc.zysoft.spring.security.util.SecurityContextUtil;
import com.starit.common.dao.hibernate.HibernateBaseDaoImpl;

/**
 * @author 袁凤建
 * <br />邮箱: yuan.fengjian@ustcinfo.com
 * <br />描述: DepartmentPcDao.java
 * <br />版本: 1.0.0
 * <br />日期: 2013-9-5 上午9:46:31
 * <br />CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */

@Repository
public class DepartmentPcDao extends HibernateBaseDaoImpl<Department, String> {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 获取部门树-部门管理列表展示.
	 * @param companyId 企业ID
	 * @param parentDepartmentId 父部门ID
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> tree(String companyId, String parentDepartmentId) {
		String sql = 
				" SELECT dept.*, comp.company_name AS companyName, 'folder' AS iconCls, dept.department_id AS id ,dept.department_name AS text," +
				" (CASE dept.have_child_deparment WHEN 'N' THEN 'open' WHEN 'Y' THEN 'closed' ELSE 'open' END) AS state, " +
				" (CASE dept.have_child_deparment WHEN 'N' THEN TRUE WHEN 'Y' THEN FALSE ELSE TRUE END) AS isLeaf, " +
				" (SELECT COUNT(*) FROM tb_c_employee emp WHERE emp.del_flag = '0' AND emp.department_id = dept.department_id) AS empNum " +
				" FROM tb_c_department dept, tb_c_company comp " +
				" WHERE dept.del_flag='0' AND dept.company_id = comp.company_id AND dept.company_id = ? AND dept.parent_department_id = ? " +
				" ORDER BY dept.display_order ";
		return this.jdbcTemplate.queryForList(sql, companyId, parentDepartmentId);
	}
	
	/**
	 * 根据部门名称查询部门信息.
	 * @param deptName 部门名称
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> searchDept(String deptName) {
		String companyId = SecurityContextUtil.getCompanyId();
		String sql = "SELECT a.* FROM tb_c_department a WHERE a.del_flag='0' AND a.company_id = ? AND a.department_name like '%" + deptName.trim() + "%'";
		return this.jdbcTemplate.queryForList(sql, companyId);
	}
	
	/**
	 * 通过部门ID查找对应的部门.
	 * @param deptId 部门ID
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> searchDeptByDeptId(String deptId) {
		String sql = "SELECT * FROM tb_c_department dept WHERE dept.del_flag='0' AND dept.department_id = ? ";
		return this.jdbcTemplate.queryForList(sql, deptId);
	}
	
	/**
	 * 获取部门树.
	 * @param parentDepartmentId 父部门ID
	 * @param companyId 企业ID
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> deptTree(String parentDepartmentId, String companyId) {
		String sql = 
				" SELECT a.department_id AS id, a.department_name AS text, a.department_type AS attributes, a.have_child_deparment AS haveChildDept " +
				" FROM tb_c_department a " +
				" WHERE a.del_flag = '0' AND a.company_id = ? AND a.parent_department_id = ? " +
				" ORDER BY a.display_order ";
		return this.jdbcTemplate.queryForList(sql, companyId, parentDepartmentId);
	}
	
	/**
	 * 检查部门名称是否已存在.
	 * @param companyId 企业ID
	 * @param departmentId 部门ID
	 * @param departmentName 部门名称
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> checkDepartmentName(String companyId, String departmentId, String departmentName) {
		String sql = " SELECT a.* FROM tb_c_department a WHERE a.del_flag = '0' AND a.company_id = ? AND a.department_name = ? ";
		if(StringUtils.hasText(departmentId)) {
			sql += " AND a.department_id != '" + departmentId + "'";
		}
		return this.jdbcTemplate.queryForList(sql, companyId, departmentName);
	}
	
	/**
	 * 根据父部门ID和企业ID获取子部门.
	 * @param parentDepartmentId 父部门ID
	 * @param companyId 企业ID
	 * @return List<Map<String, Object>>
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> departmentTree(String parentDepartmentId, String companyId) {
		String hql = "select new Map(dept.departmentId as id, dept.departmentName as text, dept.haveChildDeparment as haveChildDept)" +
				" from Department dept where dept.delFlag = '0' and dept.parentDepartmentId = ? and dept.companyId = ? " +
				" order by dept.displayOrder ";
		return this.findByHQL(hql, parentDepartmentId, companyId);
	}
	
	/**
	 * 根据父部门ID获取子部门.
	 * @param parentDepartmentId 父部门ID
	 * @return List<Map<String, Object>>
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> departmentTree(String parentDepartmentId) {
		String hql = "select new Map(dept.departmentId as id, dept.departmentName as text, dept.haveChildDeparment as haveChildDept)" +
				" from Department dept where dept.delFlag = '0' and dept.parentDepartmentId = ? order by dept.displayOrder";
		return this.findByHQL(hql, parentDepartmentId);
	}
}